using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{
    class BookDAO:DBAccess
    {
        #region sql command
        string SQL_SELECT_ID = "select max(BookID) from Book";
        string SQL_SELECT_ALL = "select * from Book";
        string SQL_SELECT_BY_CATEGORY = "select * from Book where TypeID=@typeId";
        string SQL_SELECT_BY_SHELF = "select * from Book where BookSheflID=@shelfId";
        string SQL_INSERT = "insert into Book(TypeID,[BookShelfID],[Name],[Author],[Publisher],[PublishYear],[Description],[Filename],[Rank]) values (@typeId, @shelfId,@name,@author,@publisher,@publishYear,@description,@filename,@rank)";
        string SQL_UPDATE = "update Book set TypeID=@typeId, BookShelfID=@sheftId, [Name]=@name,[Author]=@author,[Publisher]=@publisher, [PublishYear]=@publishYear,Description=@description, [Filename]=@filename, [Rank]=@rank where BookshelfID=@id";
        string SQL_DELETE = "delete from Book where BookID=@id";
        #endregion
        #region field
        string message;
        #endregion
        #region properties
        public BookDAO()
        {
            this.message = base.Message;
        }
        #endregion
        #region properties
        public  string  Message
        {
            get { return message; }
        }
        #endregion
        #region constructor
       
        #endregion
        #region methods
        public IList<Book> GetAll()
        {
            IList<Book> list = new List<Book>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_ALL, conn);
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();

                    while (dr.Read())
                    {
                        Book book = new Book();
                        book.ID = dr.GetInt32(0);
                        book.Category.CategoryID = dr.GetInt32(1);
                        book.Shelf.BookShelfID = dr.GetInt32(2);
                        book.Name = dr.GetString(3);
                        book.Author = dr.GetString(4);
                        book.Publisher = dr.GetString(5);
                        book.PublishYear = dr.GetInt32(6);
                        book.Description = dr.GetString(7);
                        book.Filename = dr.GetString(8);
                        book.Rank = dr.GetByte(9);
                        list.Add(book);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        public IList<Book> GetBookByCategory(BookCategory cate)
        {
            IList<Book> list = new List<Book>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_CATEGORY, conn);
                command.Parameters.Add("@typeId", OleDbType.Integer).Value = cate.CategoryID;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();

                    while (dr.Read())
                    {
                        Book book = new Book();
                        book.ID = dr.GetInt32(0);
                        book.Category.CategoryID = dr.GetInt32(1);
                        book.Shelf.BookShelfID = dr.GetInt32(2);
                        book.Name = dr.GetString(3);
                        book.Author = dr.GetString(4);
                        book.Publisher = dr.GetString(5);
                        book.PublishYear = dr.GetInt32(6);
                        book.Description = dr.GetString(7);
                        book.Filename = dr.GetString(8);
                        book.Rank = dr.GetByte(9);
                        list.Add(book);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        public IList<Book> GetBookOnBookshelf(BookShelf shelf)
        {
            IList<Book> list = new List<Book>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_SHELF, conn);
                command.Parameters.Add("@shelfId", OleDbType.Integer).Value = shelf.BookShelfID;
                if (Connect())
                {
                    command.Parameters.Add("@shelfId", OleDbType.Integer).Value = shelf.BookShelfID;
                    OleDbDataReader dr = command.ExecuteReader();

                    while (dr.Read())
                    {
                        Book book = new Book();
                        book.ID = dr.GetInt32(0);
                        book.Category.CategoryID = dr.GetInt32(1);
                        book.Shelf.BookShelfID = dr.GetInt32(2);
                        book.Name = dr.GetString(3);
                        book.Author = dr.GetString(4);
                        book.Publisher = dr.GetString(5);
                        book.PublishYear = dr.GetInt32(6);
                        book.Description = dr.GetString(7);
                        book.Filename = dr.GetString(8);
                        book.Rank = dr.GetByte(9);
                        list.Add(book);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        public int Insert(Book book)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_INSERT,conn);            
            if (Connect())
            {
                command.Parameters.Add("@typeId", OleDbType.Integer).Value = book.Category.CategoryID;
                command.Parameters.Add("@shelfId", OleDbType.Integer).Value = book.Shelf.BookShelfID;
                command.Parameters.Add("@name", OleDbType.VarChar).Value = book.Name;
                command.Parameters.Add("@author", OleDbType.VarChar).Value = book.Author;
                command.Parameters.Add("@publisher", OleDbType.VarChar).Value = book.Publisher;
                command.Parameters.Add("@publishYear", OleDbType.Integer).Value = book.PublishYear;
                command.Parameters.Add("@description", OleDbType.VarChar).Value = book.Description;
                command.Parameters.Add("@filename", OleDbType.VarChar).Value = book.Filename;
                command.Parameters.Add("@rank", OleDbType.TinyInt).Value = book.Rank;
        
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = SQL_SELECT_ID;
                book.ID = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
            return effectedRows;
        }
        public int Update(Book book)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
            if (Connect())
            {
                command.Parameters.Add("@typeId", OleDbType.Integer).Value = book.Category.CategoryID;
                command.Parameters.Add("@shelfId", OleDbType.Integer).Value = book.Shelf.BookShelfID;
                command.Parameters.Add("@name", OleDbType.VarChar).Value = book.Name;
                command.Parameters.Add("@author", OleDbType.VarChar).Value = book.Author;
                command.Parameters.Add("@publisher", OleDbType.VarChar).Value = book.Publisher;
                command.Parameters.Add("@publishYear", OleDbType.Integer).Value = book.PublishYear;
                command.Parameters.Add("@description", OleDbType.VarChar).Value = book.Description;
                command.Parameters.Add("@filename", OleDbType.VarChar).Value = book.Filename;
                command.Parameters.Add("@rank", OleDbType.TinyInt).Value = book.Rank;
                command.Parameters.Add("@id", OleDbType.Integer).Value = book.ID;
                effectedRows = command.ExecuteNonQuery();               
                Disconnect();
            }
            return effectedRows;
        }
        public int Delete(Book book)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
            if (Connect())
            {
                command.Parameters.Add("@id", OleDbType.Integer).Value = book.ID;
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
            return effectedRows;
        }
        #endregion
    }
}
